import pandas as pd
import numpy as np
import os
try:
    import pandas.io.formats.excel as fmt_xl     # version >= 0.20
except ImportError:
    try:
        import pandas.formats.format as fmt_xl   # 0.18 <= version < 0.20
    except ImportError:
        import pandas.core.format as fmt_xl      # version < 0.18
def df_to_excel(data,url,name=None,fontsize=10):
    d={}
    if "." not in url:
        url=url+".xlsx"
    f=os.path.dirname(url)
    if not os.path.exists(f):
        os.mkdir(f)
    writer = pd.ExcelWriter(url,engine="xlsxwriter",mode="w")
    header_format = writer.book.add_format({
        'bold': True,
        'text_wrap': True,
        'valign': 'top',
        "font_name": "微软雅黑",
        "font_size":fontsize,
        'border': 1})
    fmt = writer.book.add_format({"font_name": "微软雅黑","font_size":fontsize})
    if isinstance(data,pd.DataFrame):
        if name is None:
            name="Sheet1"
        d[name]=data
    elif isinstance(data,dict):
        for name,v in data.items():
            if isinstance(v,pd.DataFrame):
                d[name] = v
            else:
                d[name] = pd.DataFrame(v)
    else:
        if name is None:
            name="Sheet1"
        d[name]=pd.DataFrame(data)
    for name,df in d.items():
        df.to_excel(writer, index=False,sheet_name=name)
        worksheet = writer.sheets[name]

        # 从A1单元格开始写出一行数据，指定样式为header_format
        worksheet.write_row(0, 0, df.columns, header_format)
        worksheet.set_column('A:Z', None, fmt)
        worksheet.set_row(0, None, fmt)
        worksheet.freeze_panes("A2")
        #  计算表头的字符宽度
        column_widths = (
            df.columns.to_series()
                .apply(lambda x: len(str(x).encode('gbk'))).values
        )
        #  计算每列的最大字符宽度
        try:
            max_widths = (
                df.astype(str)
                    .map(lambda x: len(str(x).encode('gbk')))
                    .agg("max").values
            )
        except Exception as e:
            max_widths = (
                df.astype(str)
                    .applymap(lambda x: len(str(x).encode('gbk')))
                    .agg(max).values
            )
        # 计算整体最大宽度
        widths = np.max([column_widths, max_widths], axis=0)
        for i, width in enumerate(widths):
            worksheet.set_column(i, i, width+1)
    try:
        writer.save()
    except Exception as e:
        pass

    writer.close()
if __name__=="__main__":
    from jili.core import load
    import pandas as pd
    d=load(r"F:\qjs\data\future_base_info_20240531.pkl")
    d=pd.DataFrame(d).T
    df_to_excel(d,r"D:\ss\order.xlsx")